Introduction to Power BI Data Sources

Course: Microsoft Power BI Training

Topic: Introduction to Power BI Data Sources

Course Link: Microsoft Power BI Training at JustAcademy

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Power BI Data Sources

Microsoft Power BI is a business intelligence and data visualization platform that helps users connect to different data sources, transform raw data, build data models, create interactive reports, and generate meaningful business insights.

A data source is the original location from which Power BI obtains information. It may be an Excel workbook, CSV file, SQL database, cloud application, website, SharePoint list, data warehouse, or another supported system.

For example, a retail company may store sales transactions in SQL Server, product details in Excel, and customer information in a cloud application. Power BI can connect to these sources and help analysts combine the relevant information into a single reporting solution.

Understanding data sources is one of the first steps in learning Power BI because the quality, structure, accessibility, and update frequency of the source data affect the accuracy and reliability of the final report.

1.1 Why Are Data Sources Important?

• They provide the raw information required for reporting and analysis.
• They help organizations combine information from different departments.
• They determine which connection methods are available.
• They influence how data is refreshed and maintained.
• They affect report performance and data-model design.
• They help analysts identify data-quality problems before building visualizations.
• They support business decisions using consistent and reliable information.

2. Learning Objectives

After completing this topic, learners should be able to:

• Explain what a Power BI data source is.
• Identify the major categories of Power BI data sources.
• Understand file-based, database, cloud, web, and online-service sources.
• Connect to a data source using the Get Data experience.
• Use the Navigator window to select tables and data objects.
• Understand the difference between loading data and transforming data.
• Explain Import mode, DirectQuery, and live connections at a basic level.
• Understand data credentials, permissions, and authentication.
• Recognize the role of Power Query in data preparation.
• Understand data refresh and gateway requirements.
• Identify common connection problems and their solutions.
• Select an appropriate data source and connection method for a reporting requirement.

3. What Is a Data Source?

A data source is a file, application, database, service, or storage system that contains information used by Power BI.

Power BI connects to the source through an appropriate connector. Depending on the source and connection mode, it may import data into the semantic model, query the source when report users interact with visuals, or connect to an existing analytical model.

3.1 Examples of Data Sources

3.2 Data Source, Dataset, and Semantic Model

These terms are related, but they do not mean exactly the same thing.

• Data source: The original system or location containing information.
• Query: The instructions Power Query uses to retrieve and prepare data.
• Semantic model: The modeled data and analytical definitions used by Power BI reports, including tables, relationships, measures, and relevant connection information.
• Report: A collection of interactive pages containing visualizations based on a semantic model.
• Dashboard: A Power BI service feature that presents important information on a single canvas, often using pinned tiles.

For example, a SQL Server database may be the data source. Power Query retrieves and prepares selected tables. Power BI uses the resulting model to support a report containing sales charts and performance indicators.

4. Major Categories of Power BI Data Sources

Power BI supports a wide range of connectors. The connectors available to you depend on the Power BI product, version, environment, permissions, and source system. Common categories include the following.

4.1 File-Based Data Sources

File-based sources are useful when data is stored in documents or exported files rather than accessed directly from an operational database.

• Excel workbooks (.xlsx, .xlsm)
• Text and CSV files (.txt, .csv)
• XML files
• JSON files
• PDF files, where supported content can be extracted
• Parquet files
• Folders containing multiple files
• SharePoint folders

Example: A sales team exports its monthly transactions to CSV. An analyst connects Power BI to the file, checks the column types, cleans the records, and creates a sales report.

4.2 Database Data Sources

Database sources store structured or semi-structured information and are common in business reporting environments.

• Microsoft SQL Server
• MySQL
• PostgreSQL
• Oracle Database
• IBM Db2
• Teradata
• Snowflake
• Amazon Redshift
• Google BigQuery

Example: An organization stores customer orders in SQL Server. Power BI connects to the database to analyze sales by product, customer, and region.

Not every database connector supports the same connection modes or features. Always check the connector documentation before designing a solution.

4.3 Microsoft Excel as a Data Source

Excel is one of the most common starting points for Power BI learners because it is widely used to organize business information.

An Excel workbook may contain:

• Worksheet data.
• Named ranges.
• Excel tables.
• Pivot-related structures or data models, depending on the connection method.
• Data imported into Excel from other systems.

Example: A workbook contains columns for Order ID, Order Date, Product, Region, Quantity, and Sales Amount. Power BI can use this information to create a sales analysis report.

Best practice: Convert a clean range into an Excel Table with meaningful column names. Avoid merged cells, repeated headers, and unnecessary blank rows in the source data.

4.4 CSV and Text Files

CSV stands for Comma-Separated Values. It is a plain-text format in which records are typically arranged in rows and fields are separated by delimiters.

Example CSV content:

OrderID,OrderDate,Region,SalesAmount
1001,2026-01-05,West,120000
1002,2026-01-12,North,45000
1003,2026-02-03,South,10000

Power BI can import the file and interpret its contents as a table. Analysts should check the delimiter, encoding, headers, date formats, decimal separators, and numeric data types.

4.5 Web Data Sources

The Web connector can retrieve supported content from web locations. Depending on the site and authentication requirements, that content may include structured tables, files, or data exposed through an accessible endpoint.

Typical uses include:

• Analyzing publicly available structured information.
• Retrieving supported data from an organization website.
• Connecting to a suitable web API.
• Combining web-based data with internal business information.

Important: A website being publicly viewable does not guarantee that its data can be reliably imported. Websites may use dynamic content, authentication, anti-automation controls, or formats that require additional processing. Always follow the website's terms and access requirements.

4.6 Cloud Data Sources

Cloud sources store information in online databases, storage services, or hosted data platforms.

Examples include:

• Azure SQL Database.
• Azure Data Lake Storage.
• Azure Synapse Analytics.
• Microsoft Fabric Lakehouse and Warehouse items.
• Cloud-hosted Snowflake databases.
• Cloud analytics services with supported Power BI connectors.

Cloud connections can simplify access for distributed teams, but organizations must still configure credentials, permissions, networking, and refresh settings correctly.

4.7 Online Services and Business Applications

Power BI has connectors for various business applications and online services. The specific connectors available depend on the current connector catalog and product configuration.

Examples may include:

• Microsoft SharePoint Online.
• Microsoft Dynamics 365 and Dataverse.
• Salesforce.
• Google Analytics.
• Other supported business and analytics services.

These connections help organizations analyze information that is distributed across sales, marketing, customer service, finance, and operational applications.

4.8 Power BI and Microsoft Fabric Data Sources

Power BI can connect to existing analytical assets, including supported Power BI semantic models and Microsoft Fabric data items. This can reduce duplicated modeling work and help teams reuse governed data.

Examples include:

• Existing Power BI semantic models.
• Supported dataflows.
• Microsoft Fabric Lakehouses.
• Microsoft Fabric Warehouses.
• Supported analytical databases and data items.

The available connection mode and modeling capabilities vary by source. Some connections reuse an existing model rather than importing raw tables into a new model.

5. Understanding the Get Data Feature

Get Data is the starting point for connecting Power BI Desktop to a data source. It provides access to available connectors and begins the process of retrieving data.

5.1 Steps to Open Get Data

1. Open Power BI Desktop.
2. Open the Home tab on the ribbon.
3. Select Get data or open its dropdown menu.
4. Choose a commonly used source or select More to explore additional connectors.
5. Find the required connector in the available categories or search for it.
6. Select the connector and choose Connect.

The exact interface may vary by Power BI Desktop version and enabled features. Microsoft's newer Power Query Get Data experience and the legacy dialog may organize connectors differently.

5.2 Common Get Data Categories

Connector categories and the list of available sources can change as Microsoft adds features and updates Power BI.

6. Connecting to an Excel Data Source

This section explains how to connect Power BI Desktop to an Excel workbook.

6.1 Prepare the Excel File

1. Open the Excel workbook.
2. Ensure the first row contains meaningful column headings.
3. Remove unnecessary blank rows and columns.
4. Check that each column contains a consistent type of information.
5. Convert the data range into an Excel Table when appropriate.
6. Save the workbook in an accessible location.

6.2 Connect to the Workbook

1. Open Power BI Desktop.
2. Select Home and then Get data.
3. Choose Excel workbook.
4. Browse to the required workbook.
5. Select the file and choose Open.
6. Wait for the Navigator window to display the available worksheets, tables, or supported data objects.
7. Select the required table or worksheet.
8. Choose Load to import the data directly, or Transform Data to review and prepare it before loading.

6.3 Example Excel Dataset

After loading the table, the fields become available for data modeling and report creation. For example, Product can be used as a category and Sales Amount can be used to calculate total sales.

7. Connecting to a CSV File

CSV files are often used when systems export records in a simple tabular format.

7.1 Connection Steps

1. Open Power BI Desktop.
2. Select Get data.
3. Choose Text/CSV.
4. Browse to the CSV file.
5. Review the preview and confirm the delimiter and encoding.
6. Select Load or Transform Data.
7. Check column headers and data types after import.

7.2 Common CSV Issues

• Columns are combined because the delimiter is incorrect.
• Dates are interpreted using the wrong regional format.
• Decimal values are parsed incorrectly because of locale settings.
• Special characters display incorrectly because of encoding differences.
• Blank rows or inconsistent column counts create data-quality problems.

Inspect the preview carefully before loading the file. If the format is inconsistent, use Power Query to correct the data or repair the source file.

8. Connecting to a SQL Server Database

SQL Server is a relational database system widely used to store structured business information. Power BI can connect to SQL Server to retrieve selected tables, views, or query results, depending on the connection configuration.

8.1 Information Required

• Server name or address.
• Database name, where applicable.
• Authentication method and authorized credentials.
• Network access to the database server.
• Appropriate database permissions.
• The required connection mode, such as Import or DirectQuery where supported.

8.2 Connection Steps

1. Open Power BI Desktop.
2. Select Get data and choose SQL Server database.
3. Enter the server name.
4. Enter the database name if you want to connect directly to a particular database.
5. Choose an available connection mode appropriate for the source and reporting requirements.
6. Select OK or Connect.
7. Provide the required authentication information.
8. Review the Navigator window.
9. Select the necessary tables or views.
10. Choose Load or Transform Data.

8.3 Example SQL Tables

A business database may contain these tables:

• Customers: CustomerID, CustomerName, City.
• Products: ProductID, ProductName, Category.
• Orders: OrderID, CustomerID, OrderDate.
• OrderDetails: OrderID, ProductID, Quantity, UnitPrice.

Power BI can use related tables to analyze sales by customer, product, date, and location. Relationships and appropriate measures help produce meaningful results.

9. Connecting to Web Data

The Web connector can be used when information is exposed through a supported web source. The connection process depends on whether the data is a public table, a downloadable file, or an API requiring authentication.

9.1 General Steps

1. Open Power BI Desktop.
2. Select Get data.
3. Choose Web.
4. Enter the URL of the supported source.
5. Choose the appropriate authentication method if prompted.
6. Review the available data objects in Navigator or the preview experience.
7. Select the relevant object.
8. Choose Transform Data to inspect and prepare the data.
9. Load the prepared data into the model.

9.2 Example Use Cases

• Analyzing structured public economic data.
• Importing supported tables from a website.
• Retrieving data from an accessible web endpoint.
• Combining public data with internal sales information.

9.3 Important Considerations

• Check whether the source permits automated access.
• Verify that the URL returns a supported format.
• Understand any authentication or API requirements.
• Confirm whether the connection will continue to work after publishing.
• Review refresh behavior and source-side rate limits.

10. Understanding the Navigator Window

Navigator appears during many connection workflows and lets users inspect and select available data objects from the source.

10.1 What Navigator Does

• Lists available tables, worksheets, views, or other supported objects.
• Displays a preview of selected data where supported.
• Allows users to choose which objects to bring into the model.
• Provides a convenient way to select only the data required for the report.

10.2 How to Use Navigator

1. Connect to the selected source.
2. Wait for Power BI to retrieve the available objects.
3. Expand the relevant groups or folders if necessary.
4. Select the required tables or data objects.
5. Review the preview to confirm the content is correct.
6. Choose Load for direct loading or Transform Data for preparation.

Best practice: Select only the tables and columns required for the reporting task. Loading unnecessary information can complicate the model and may increase resource usage.

11. Load Data Versus Transform Data

When connecting to many data sources, Power BI gives users the option to load the selected data or open Power Query to transform it first.

11.1 Common Data Transformations

• Rename columns.
• Change data types.
• Remove unnecessary columns.
• Filter irrelevant rows.
• Replace incorrect values.
• Remove duplicate rows where appropriate.
• Split or merge columns.
• Combine files with compatible structures.
• Merge queries using matching keys.
• Append rows from compatible tables.
• Pivot or unpivot columns to reshape data.

11.2 Example of Data Cleaning

Suppose an Excel file contains the following values:

An analyst may remove the final row if it is an unnecessary blank record, assign the correct numeric data type to Sales Amount, and rename fields for clarity. The decision to remove a row should depend on the data's meaning rather than appearance alone.

12. Introduction to Power Query

Power Query is the data connection and transformation technology used by Power BI and other Microsoft products. It helps users retrieve, clean, reshape, and combine data before it is used for reporting.

12.1 Why Power Query Is Useful

• It reduces the need to manually clean data every time a report is updated.
• It records transformation steps in a repeatable process.
• It supports data preparation from many different sources.
• It can combine multiple files or tables.
• It helps make data consistent before modeling.

12.2 Applied Steps

Power Query records many transformations as Applied Steps. When a query refreshes, Power Query evaluates the configured steps again against the source data, subject to the connector and query configuration.

For example, a query might contain these steps:

1. Connect to the Excel workbook.
2. Select the Sales table.
3. Promote the first row to headers if necessary.
4. Set the Order Date column to Date.
5. Set Sales Amount to a numeric or currency-compatible type.
6. Remove irrelevant columns.
7. Filter out invalid records according to defined business rules.
8. Load the resulting table.

12.3 Introduction to M Language

Power Query uses the M language to define data retrieval and transformation steps. Most everyday transformations can be created through the user interface without manually writing M code.

A simplified M example that references a named Excel table is shown below:

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(
        Source,
        {
            {"OrderID", Int64.Type},
            {"Region", type text},
            {"SalesAmount", Currency.Type}
        }
    )
in
    ChangedTypes

This example assumes an Excel table named Sales containing the specified columns. The query retrieves the table and assigns types to selected fields. Actual M code depends on the connector and the structure of the source.

13. Understanding Connection Modes

Power BI supports different ways of accessing data. The appropriate method depends on the source connector, data volume, refresh requirements, performance expectations, and modeling needs.

13.1 Import Mode

Import mode copies selected data into the Power BI semantic model. Reports generally query the imported model when users interact with visuals.

Advantages:

• Often provides responsive report interactions.
• Supports a wide range of modeling and analytical features.
• Can reduce the number of queries sent to the source during report interaction.

Considerations:

• Imported data must be refreshed to reflect source changes.
• Model size and available capacity matter.
• Refresh time depends on source size, transformations, and environment.

Example: Import monthly sales data from Excel into a report model for regular business analysis.

13.2 DirectQuery

DirectQuery allows Power BI to query a supported underlying source rather than importing all of the source data into the model in the same way as Import mode. Report interactions may trigger queries against the source.

Advantages:

• Can support analysis over data that remains in the underlying system.
• Can be useful when importing all required data is impractical.
• Can reduce the need for a full imported copy of source data.

Considerations:

• Report responsiveness depends on source performance and query design.
• Some modeling and transformation capabilities differ from Import mode.
• Source workload and concurrency should be considered.
• Connector-specific limitations apply.

Example: A report connects to a supported SQL database containing a large transaction table and queries the source as users explore the report.

13.3 Live Connection

A live connection connects Power BI to an existing supported semantic or analytical model, such as a Power BI semantic model or SQL Server Analysis Services model, rather than importing the underlying data as a new standalone model.

Advantages:

• Can reuse centrally managed measures and relationships.
• Helps maintain consistent business definitions across reports.
• Can reduce duplicated modeling effort.

Considerations:

• Modeling and editing options depend on the connected source and connection capabilities.
• Users may need permission to access the existing model.
• Some changes must be made in the source model rather than the report.

13.4 Comparison of Connection Modes

These modes are not interchangeable in every scenario. Review the connector's documented capabilities before choosing a connection strategy.

14. Choosing the Right Data Source

The best source depends on the business requirement, data availability, update frequency, security needs, and technical environment.

14.1 Questions to Ask Before Connecting

• Where is the data currently stored?
• Who owns and maintains the source?
• Is the source structured and reliable?
• How much data needs to be analyzed?
• How frequently does the data change?
• Does the report need imported data or source-side querying?
• Are the required connector and connection mode supported?
• What authentication and permissions are required?
• Will the report be refreshed in Power BI Desktop or Power BI Service?
• Is a gateway or additional network configuration required?

15. Data Credentials and Authentication

Power BI may require credentials before it can connect to a source. Authentication confirms the identity of the user or service accessing the data.

15.1 Common Authentication Methods

• Organizational account: Uses a supported organizational identity to access services.
• Database authentication: Uses database credentials where supported.
• Windows authentication: Uses Windows credentials in supported environments.
• Basic authentication: Uses a username and password where the connector supports it.
• API key or other credential: Uses a source-specific authentication mechanism where available.
• Anonymous access: Used only when the source supports access without credentials.

The available authentication options depend on the connector and source. Some services require additional configuration, such as OAuth-based sign-in, application permissions, or an administrator-approved connection.

15.2 Data Source Permissions

A successful connection requires appropriate permissions. A user may be able to open a file manually but still lack the permissions needed for Power BI to access it through a connector or refresh it in the Power BI Service.

Check the following:

• The account has permission to access the source.
• The database user has access to the required tables or views.
• The cloud account has access to the correct workspace or resource.
• The authentication method matches the source configuration.
• Credentials are configured correctly in the environment where refresh occurs.

15.3 Security Best Practices

• Never share passwords or API keys in report documentation.
• Use authorized accounts and least-privilege permissions.
• Follow organizational policies for credential storage.
• Review access before sharing reports.
• Protect confidential information in both source systems and Power BI.
• Use approved organizational authentication methods wherever applicable.

16. Data Refresh in Power BI

Data refresh updates imported data or refreshes the relevant source connection according to the configured connection mode. The exact process depends on the source, model, and Power BI environment.

16.1 Why Refresh Is Necessary

Business data changes continuously. New orders are placed, customers are added, and inventory levels change. If an imported model is not refreshed, its values may no longer match the current source data.

16.2 Types of Refresh

• Refresh in Power BI Desktop: Updates the data used by the local report model according to its queries and configuration.
• Manual refresh in Power BI Service: Starts a refresh for a published semantic model when supported and permitted.
• Scheduled refresh: Runs refreshes at configured times when the model, license or capacity, source, and settings support it.
• Automatic or source-managed updates: Some cloud and connected-model scenarios use different update behavior, depending on the connection type.

16.3 Factors Affecting Refresh

• Source availability.
• Network connectivity.
• Authentication and credential validity.
• Gateway configuration for applicable sources.
• Query complexity and data volume.
• Source system performance.
• Refresh limits and capacity settings.

17. Understanding the On-Premises Data Gateway

An on-premises data gateway allows supported cloud services, including Power BI Service, to securely access certain data sources located inside an organization's network.

For example, an organization may keep its SQL Server database on an internal company server while publishing reports to the Power BI Service. A correctly configured gateway can enable supported refresh or query scenarios between the cloud service and the on-premises source.

17.1 When a Gateway May Be Needed

• When a published semantic model must refresh from a supported on-premises database.
• When the Power BI Service needs access to an internal file or server-based source.
• When a supported live or DirectQuery scenario requires gateway connectivity.

17.2 Basic Gateway Workflow

1. Install the appropriate gateway on a supported computer or server.
2. Sign in and register the gateway according to organizational requirements.
3. Configure the data source connection and credentials.
4. Map the published semantic model to the appropriate gateway data source.
5. Test the connection and refresh configuration.
6. Monitor refresh history and gateway availability.

Not every data source requires an on-premises gateway. Many cloud-to-cloud connections can connect directly, while specific connectors and network configurations may have additional requirements.

18. Combining Multiple Data Sources

Business reporting often requires information from several systems. Power BI can bring together data from supported sources, but the tables must be prepared and modeled carefully.

18.1 Example: Sales Reporting System

Consider a company with the following sources:

18.2 General Process

1. Connect to each required source.
2. Select only relevant tables and fields.
3. Clean and standardize the data in Power Query.
4. Confirm that keys used to connect tables are consistent.
5. Create suitable relationships in the model.
6. Develop measures for sales, targets, and performance.
7. Build report visuals and test the results.

18.3 Merge Versus Append

Power Query provides different ways to combine data.

• Merge queries: Joins information from two tables using matching columns, similar in concept to joining tables in SQL.
• Append queries: Adds rows from one table to another table with compatible column structures.

Example of a merge: Add product category information to sales records using ProductID.

Example of an append: Combine January sales and February sales into a single table when both files have compatible columns.

19. Data Quality and Validation

Connecting to a source is only the beginning. Analysts must verify that the data is complete, consistent, and suitable for analysis.

19.1 Common Data-Quality Problems

• Missing or blank values.
• Duplicate transactions.
• Inconsistent category names.
• Incorrect data types.
• Invalid dates.
• Incorrect currency or decimal formats.
• Unmatched keys between related tables.
• Unexpected totals caused by duplicate records or incorrect relationships.

19.2 Data Validation Checklist

• Compare row counts with the source where possible.
• Check for duplicate identifiers when uniqueness is expected.
• Validate date ranges.
• Check that numeric fields contain valid values.
• Confirm that required fields are populated.
• Compare key totals with trusted source reports.
• Verify relationships and matching keys.
• Document any assumptions or data-cleaning rules.

20. Common Data Source Connection Errors

20.1 Incorrect File Path

Problem: Power BI cannot locate a workbook or CSV file.

Possible solutions:

• Verify the file path and filename.
• Confirm that the file still exists.
• Check access permissions.
• When publishing, confirm that the Power BI Service can reach the source through the supported configuration.

20.2 Invalid Credentials

Problem: The connection fails because authentication is unsuccessful.

Possible solutions:

• Verify the selected authentication method.
• Confirm that the account has permission to access the source.
• Update credentials through the appropriate data source settings.
• Check whether the organization requires multifactor authentication or administrator approval.

20.3 Database Connection Failure

Problem: Power BI cannot connect to the database server.

Possible solutions:

• Check the server name and database name.
• Confirm that the server is running.
• Verify network and firewall access.
• Check database permissions and authentication.
• Confirm that the required connector and connection mode are supported.

20.4 Data Type Errors

Problem: Power BI cannot correctly interpret some values.

Possible solutions:

• Inspect the source column values.
• Check regional settings and locale-specific formats.
• Set the correct type in Power Query.
• Replace or handle invalid values using documented business rules.

20.5 Refresh Failure After Publishing

Problem: A report works in Power BI Desktop but refresh fails in the Power BI Service.

Possible solutions:

• Review the refresh error details.
• Check cloud data source credentials in the service.
• Verify gateway availability for applicable on-premises sources.
• Confirm that the published model can access the source.
• Check whether the source path depends on a local computer or user-specific location.
• Review source permissions, refresh configuration, and applicable service limits.

21. Best Practices for Working with Power BI Data Sources

• Understand the source system before connecting to it.
• Choose a connector designed for the data source.
• Use clear table and column names.
• Import only the information required for the report.
• Clean and standardize data before building visuals.
• Check connector documentation for supported capabilities and limitations.
• Select Import, DirectQuery, or a live connection based on the business requirement.
• Use a consistent data-refresh strategy.
• Keep credentials secure and use authorized access.
• Validate the results after data transformations.
• Document source ownership, refresh frequency, and data assumptions.
• Test the report after publishing.
• Monitor refresh failures and resolve source connection problems promptly.

22. Practical Example: Building a Sales Report from Excel

Objective: Connect Power BI to an Excel sales file and prepare the data for analysis.

22.1 Sample Data

22.2 Step 1: Connect to Excel

Open Power BI Desktop, select Get data, choose Excel workbook, and select the prepared file.

22.3 Step 2: Select the Data

Use Navigator to select the relevant worksheet or Excel Table. Preview the contents to confirm that the expected columns are present.

22.4 Step 3: Transform the Data

Choose Transform Data. Check that OrderID and Quantity use appropriate numeric types, OrderDate is a Date field, and SalesAmount is a suitable numeric or currency field. Remove irrelevant columns only if they are not needed for the report.

22.5 Step 4: Load the Data

After reviewing the query steps, select Close & Apply to apply the transformations and load the data into the model.

22.6 Step 5: Create a Measure

Total Sales = SUM(Sales[SalesAmount])

This expression assumes that the imported table is named Sales and that SalesAmount is a valid numeric column.

22.7 Step 6: Create a Visualization

Add a bar chart and use Region as the category and Total Sales as the value. Add a card for the overall Total Sales measure.

22.8 Step 7: Validate the Results

Check that the regional values reconcile with the expected source totals, assuming all relevant records are included and no additional filters alter the context. Save the report and document the source file location and refresh requirements.

23. Practical Exercises

Exercise 1: Identify Data Source Types

List five data sources that an organization could use with Power BI. For each source, describe the type of information it contains and the report it could support.

Exercise 2: Connect to Excel

Import an Excel workbook containing sales records. Identify the tables, columns, and data types in the imported model.

Exercise 3: Connect to CSV

Import a CSV file and investigate whether the delimiter, column headers, and date formats are interpreted correctly.

Exercise 4: Explore Navigator

Connect to a workbook with multiple worksheets or tables. Use Navigator to select only the data required for a report.

Exercise 5: Transform Data

Open Power Query Editor and rename columns, change data types, and remove unnecessary fields. Record the transformation steps.

Exercise 6: Compare Connection Modes

Research whether a supported database connector offers Import and DirectQuery. Explain the benefits and limitations of each option for a chosen reporting scenario.

Exercise 7: Investigate Refresh

Describe what must be configured when a report uses a local file or an on-premises database and needs refresh in the Power BI Service.

24. Mini Project: Connect and Prepare Business Data

Project Goal: Create a clean Power BI model from a business data source and prepare it for report development.

24.1 Project Requirements

• Choose a suitable Excel, CSV, or supported database source.
• Connect using Get Data.
• Inspect available data objects through Navigator where applicable.
• Use Power Query to correct data types and clean the data.
• Remove irrelevant fields where appropriate.
• Load the prepared data into Power BI.
• Create a basic Total Sales measure if the dataset contains a sales amount field.
• Create a table or chart to confirm that the model is usable.
• Document the source location, credentials requirements, and refresh approach.

24.2 Expected Learning Outcome

At the end of the project, learners should understand how to locate a supported data source, connect to it, select relevant data, prepare the data, load it into Power BI, and verify the result before building a complete report.

25. Interview Questions and Answers

Q1. What is a data source in Power BI?

Answer: A data source is the file, database, application, service, or other supported system from which Power BI retrieves information for analysis and reporting.

Q2. Name some common Power BI data sources.

Answer: Common examples include Excel, CSV, SQL Server, Oracle, SharePoint Online, supported web sources, Azure SQL Database, and existing Power BI semantic models.

Q3. What is the purpose of Get Data?

Answer: Get Data provides access to available connectors and starts the process of connecting Power BI to a data source.

Q4. What is the Navigator window?

Answer: Navigator lets users review and select supported tables, worksheets, views, or other data objects from a connected source.

Q5. What is Power Query?

Answer: Power Query is the technology used to connect to and transform data before it is used in Power BI models and reports.

Q6. What is the difference between Load and Transform Data?

Answer: Load imports the selected data using the current query configuration. Transform Data opens Power Query so that users can clean, reshape, and prepare the data before loading.

Q7. What is Import mode?

Answer: Import mode copies selected data into the Power BI semantic model. Imported data must be refreshed to reflect source changes.

Q8. What is DirectQuery?

Answer: DirectQuery allows Power BI to query a supported underlying source rather than importing all the source data into the model in the same way as Import mode.

Q9. What is a live connection?

Answer: A live connection connects a report to an existing supported semantic or analytical model, allowing it to reuse that model's data and definitions.

Q10. Why is data transformation necessary?

Answer: Transformation helps correct data types, remove irrelevant information, standardize values, handle missing records, and prepare data for reliable analysis.

Q11. What is a data gateway?

Answer: An on-premises data gateway enables supported cloud services to access certain data sources inside an organization's network for scenarios such as refresh or querying.

Q12. Why might a report refresh fail after publishing?

Answer: Common causes include invalid credentials, unavailable source files, missing gateway configuration, network problems, insufficient permissions, and source or refresh limitations.

Q13. Can Power BI connect to multiple data sources?

Answer: Yes. Power BI can connect to multiple supported sources and combine their information through queries, transformations, and data-model relationships.

Q14. What is the difference between Merge and Append in Power Query?

Answer: Merge joins information from tables using matching columns, while Append adds rows from one table to another compatible table.

Q15. Why should data types be checked after importing data?

Answer: Incorrect data types can cause calculation errors, incorrect sorting, invalid aggregations, and unexpected date or number filtering behavior.

26. Quick Revision Notes

• A data source is the original location of the information used in Power BI.
• Power BI supports many file, database, cloud, web, and online-service connectors.
• Get Data is used to begin a source connection.
• Navigator helps select data objects from supported sources.
• Load imports data using the current query configuration.
• Transform Data opens Power Query for data preparation.
• Power Query records transformation steps that can be reapplied during refresh.
• Import mode stores selected data in the semantic model.
• DirectQuery queries a supported underlying data source.
• Live connections reuse an existing supported analytical model.
• Credentials and permissions control access to data.
• Refresh settings determine how imported data is updated.
• A gateway may be required for supported access to on-premises sources from the cloud.
• Data validation is necessary before building reliable reports.

27. Final Checklist

• Identify the source and confirm that a suitable connector is available.
• Verify access permissions and authentication requirements.
• Select only the tables and fields needed for the report.
• Check the data preview before loading.
• Correct data types and clean the source data.
• Choose a suitable connection mode.
• Validate row counts, key values, and important totals.
• Confirm whether the report requires a gateway.
• Configure and test refresh where required.
• Document the data source and relevant assumptions.
• Test the report after publishing.

28. Official Learning Resources

• Microsoft Learn: Power BI Data Sources
• Microsoft Learn: Get Data in Power BI Desktop
• Microsoft Learn: Power BI Data Connection Documentation
• Microsoft Learn: Refresh Data in Power BI
• Microsoft Learn: On-Premises Data Gateway

29. Conclusion

Power BI data sources provide the foundation for all reporting and analysis. Learning to connect to Excel files, CSV files, databases, web sources, cloud platforms, and existing semantic models enables analysts to access the information required for business decisions.

The data connection process involves selecting a suitable connector, providing authorized credentials, choosing the relevant data objects, transforming the data where necessary, and loading or connecting to the appropriate model. Connection mode, refresh configuration, source permissions, and data quality must also be considered when designing a reliable reporting solution.

By practicing these concepts, learners can develop a strong foundation for Power Query, data modeling, DAX, visualizations, and interactive Power BI reports.

To explore structured training, visit the Microsoft Power BI Training course page at JustAcademy, or register for a course demo.